﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace DA
{
    public class baocaotongket_DA: ConnectData
    {
        public DataTable LoadDSTongKetMonHoc(string MaMH, string MaHK)
        {
            string sql = "SELECT row_number() OVER(order by l.MaLop) STT, TENLOP, b.SoHS SISO, (b.SoHS - ISNULL(DD.KhongDat,0)) SOLUONGDAT, CAST((CONVERT(float,(b.SoHS - ISNULL(DD.KhongDat,0))) /CONVERT(float,b.SoHS))*100 AS nvarchar) + ' %' TYLE FROM LOP l INNER JOIN (select MALOP, COUNT(distinct MAHS) SoHS from BANGDIEM where MAMH = '" + MaMH + "' and MAHK = '" + MaHK + "' group by MALOP ) b on l.MALOP = b.MALOP left JOIN (SELECT MALOP, COUNT(MAHS) KhongDat FROM BANGDIEM WHERE DTB < (select DIEMDATMON from QUYDINH) AND MAMH = '" + MaMH + "' and MAHK = '" + MaHK + "' GROUP BY MALOP) DD ON DD.MALOP = B.MALOP ";

            return LayDatatable(sql);
        }
    }
}
